# coding=UTF-8
import math
import time
import random
import pymssql


def exportCsv(result, name):
    fout = open(name, 'w')
    s = ''
    for c in (result):
        s = s+c+','

    fout.write(s)

    fout.close()


server = "10.160.12.192"
u = "sa"
password = "Docimax@123"

DbMrfs = "MR_FS_DB"

conn = pymssql.connect(server, u, password, DbMrfs)  # 获取连接
conn1 = pymssql.connect(server, u, password, DbMrfs)
cursor = conn.cursor()  # 获取光标
cursor1 = conn1.cursor()  # 获取光标
sql = '''select id,Totalcost,Mrid from VBase v
   where v.CodeState = 1000 
   and (v.DischargeDateTime between '2018-01-01' and '2019-11-01')  
    and (v.OutDepartCode not like '国际%')   
	and (v.OutSickRoomCode not like '国际%') and PaymentTypeCode not in ('6','7','9')
      and (v.InpatientDay < 2)'''
cursor.execute(sql)

# 遍历数据（存放到元组中） 方式1
rows = cursor.fetchall()

result = []
i = 0.0
# 遍历数据（存放到元组中） 方式2
for row in rows:
    i = i+1
    print(i/len(rows))
    ssql = "select sum(feevalue) from vfee where FeeClassName in ('化验费','放射费','核素检查','一般检查费','超声费','病理费','检查用一次性医用耗材费') and ipb_id='" + \
        row[0]+"'"
    cursor1.execute(ssql)
    rows1 = cursor1.fetchall()
    v = rows1[0][0]
    if(v != None and v/row[1] > 0.7 and row[2] not in result):
        print('OK')
        result.append(row[2])

exportCsv(result, 'fee.csv')
cursor.close()
cursor1.close()
# connmrfs = pymssql.connect(server, u, password, DbMrfs)  # 获取连接
# cursormrfs = connmrfs.cursor()  # 获取光标
# cursormrfs.execute('select top 10 * from VBase')

# 遍历数据（存放到元组中） 方式1
# rowsmrfs = cursormrfs.fetchall()
# for row in rowsmrfs:
#     print('row = %r' % (row,))

# conn = pymssql.connect(server, u, password, DbDestnation)  # 获取连接
